﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using net.windward.utils;
using net.windward.utils.ado;
using Kailua.WSSLists;
using System.Xml;
using System.Net;
using System.Collections;
using System.Web.Services.Protocols;
using log4net;

namespace Kailua.net.windward.utils.ado.SPList.custom
{
    /// <summary>
    /// A SharePoint List database.  This corresponds to a SharePoint website (SPWeb).
    /// </summary>
    public class WrSPListDatabase2 : IWrDatabase
    {
        #region Fields
        /// <summary>
        /// The url of the SPWeb.
        /// </summary>
        private string spWebUrl;
        /// <summary>
        /// The list title
        /// </summary>
        private string listTitle;
        /// <summary>
        /// A GUID string for the list's ID field.
        /// </summary>
        private string listId;

    	private WrTable[] tables;

        private static ILog log = LogManager.GetLogger(typeof(WrSPListDatabase2));
        #endregion Fields

        #region Constructors
        /// <summary>
        /// Creates a new WrSPListDatabase from the specified SPWeb url and list spec string.
        /// The list spec string is a string like "ListName, {guid}".
        /// </summary>
        /// <param name="spWebUrl"></param>
        /// <param name="listSpec"></param>
        public WrSPListDatabase2(string spWebUrl, string listSpec)
        {
            this.spWebUrl = spWebUrl;
            listTitle = string.Empty;
            listId = string.Empty;

            if (!string.IsNullOrEmpty(listSpec))
            {
                int pos = listSpec.LastIndexOf(',');
                if (pos >= 0)
                    listTitle = listSpec.Substring(0, pos);
                listId = listSpec.Substring(pos + 1);
            }
        }
        /// <summary>
        /// Creates a new WrSPListDatabase using the specified SPWeb url and list info.
        /// </summary>
        /// <param name="spWebUrl"></param>
        /// <param name="listTitle"></param>
        /// <param name="listId"></param>
        public WrSPListDatabase2(string spWebUrl, string listTitle, string listId)
        {
            this.spWebUrl = spWebUrl;
            this.listTitle = listTitle;
            this.listId = listId;
        }
        #endregion Constructors

        #region IWrDatabase Members

        /// <summary>
        /// The name of the database.
        /// </summary>
        public string Name
        {
            get { return listTitle + "," + listId; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string DefaultSchema
        {
            get { return null; }
        }

		/// <summary>
		/// All tables in this database. Must call LoadMetadata first.
		/// </summary>
		public WrTable[] Tables
		{
			get { return tables; }
		}

		/// <summary>
		/// All views in this database. Must call LoadMetadata first.
		/// </summary>
		public WrView[] Views
		{
			get { return null; }
		}

		/// <summary>
		/// Return all stored procedures in this database. Must call LoadMetadata first.
		/// </summary>
		public WrStoredProcedure[] StoredProcedures
		{
			get { return null; }
		}

    	/// <summary>
    	/// Loads the metadata for this database.
    	/// </summary>
    	/// <param name="credentials">The user credentials to access the database.</param>
    	/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(WrCredentials credentials, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
    	{
    		Trap.trap();
    		tables = GetTables(credentials, owner);
			for (int index=0; index<tables.Length; index++)
			{
				WrColumn[] columns = GetColumns(tables[index], credentials, owner);
                tables[index] = new WrSPListTable(tables[index].Name, tables[index].Description, ((WrSPListTable)tables[index]).ListID, tables[index].IsSystem, columns);
			}
    	}

    	/// <summary>
    	/// Loads the metadata for this database.
    	/// </summary>
    	/// <param name="connStr">The connection string to access the database.</param>
    	/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
    	{
			Trap.trap();
			tables = GetTables(connStr, owner);
			for (int index = 0; index < tables.Length; index++)
			{
				WrColumn[] columns = GetColumns(tables[index], connStr, owner);
                tables[index] = new WrSPListTable(tables[index].Name, tables[index].Description, ((WrSPListTable)tables[index]).ListID, tables[index].IsSystem, columns);
			}
		}

		/// <summary>
        /// Gets all the lists in the SPWeb as WrSPListTable objects.
        /// </summary>
        /// <param name="credentials"></param>
        /// <param name="owner"></param>
        /// <returns></returns>
        private WrTable[] GetTables(WrCredentials credentials, WrVendor.ELEM_OWNER owner)
        {
            try
            {
                // query list service for all lists in SPWeb
                Lists listService = CreateListsService(credentials);
                XmlNode ndLists = listService.GetListCollection();

                // create a table object for each list
                XmlDocument listsXmlDoc = new XmlDocument();
                listsXmlDoc.LoadXml(ndLists.OuterXml);
                XmlNamespaceManager nsmgr = new XmlNamespaceManager(listsXmlDoc.NameTable);
                // note: the xpath won't work unless you use a prefix, even for the default namespace
                nsmgr.AddNamespace("df", "http://schemas.microsoft.com/sharepoint/soap/");
                ArrayList tables = new ArrayList();
                foreach (XmlNode ndList in listsXmlDoc.SelectNodes("/df:Lists/df:List", nsmgr))
                {
                    WrTable newTable = null;
                    string listOnTitle = ndList.Attributes["Title"].Value;
                    string listOnId = ndList.Attributes["ID"].Value;
                    string listOnDesc = ndList.Attributes["Description"].Value;
                    newTable = new WrSPListTable(listOnTitle, listOnDesc, listOnId, false, null);
                    tables.Add(newTable);
                }

                return (WrTable[])tables.ToArray(typeof(WrTable));
            }
            catch (SoapException ex)
            {
                string detail = ex.Detail.OuterXml;
                throw new Exception(detail, ex);
            }
            catch (Exception ex)
            {
                if (log.IsWarnEnabled)
                    log.Warn("Error occured getting tables from SharePoint lists data.", ex);
                throw;
            }
        }
        /// <summary>
        /// Gets all the lists in the SPWeb as WrSPListTable objects.
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="owner"></param>
        /// <returns></returns>
		private WrTable[] GetTables(string connStr, WrVendor.ELEM_OWNER owner)
        {
            // parse connection string
            Hashtable keyPairs = SPListConnection.ParseConnectionString(connStr);
            this.spWebUrl = (string)keyPairs["URL"];
            WrCredentials credentials = WrSPListServer2.CreateWrCredentials(keyPairs);

            // run GetTables(WrCredentials, WrVendor.ELEM_OWNER)
            return GetTables(credentials, owner);
        }

        /// <summary>
        /// Gets all the fields in the SharePoint list identified by the specified table.
        /// </summary>
        /// <param name="table"></param>
        /// <param name="credentials"></param>
        /// <param name="owner"></param>
        /// <returns></returns>
		private WrColumn[] GetColumns(WrBaseTable table, WrCredentials credentials, WrVendor.ELEM_OWNER owner)
        {
            try
            {
                // get the table info
                WrSPListTable spListTable = (WrSPListTable)table;
                string listId = spListTable.ListID;

                // query list service for all columns in the table
                Lists listService = CreateListsService(credentials);
                XmlNode ndList = listService.GetList(listId);

                // create a column object for each field
                XmlDocument listXmlDoc = new XmlDocument();
                listXmlDoc.LoadXml(ndList.OuterXml);
                XmlNamespaceManager nsmgr = new XmlNamespaceManager(listXmlDoc.NameTable);
                // note: the xpath won't work unless you use a prefix, even for the default namespace
                nsmgr.AddNamespace("df", "http://schemas.microsoft.com/sharepoint/soap/");
                ArrayList columns = new ArrayList();
                foreach (XmlNode ndCol in listXmlDoc.SelectNodes("/df:List/df:Fields/df:Field", nsmgr))
                {
                    XmlAttribute atDispName = ndCol.Attributes["DisplayName"];
                    XmlAttribute atName = ndCol.Attributes["Name"];
                    //bugbug: should this ever happen?
                    if (atName == null)
                        continue;
                    string name = atName.Value;
                    string dispName = atDispName == null ? name : string.Format("{0} ({1})", atDispName.Value, name);
                    //bugbug: need the real DbType
                    WrColumn newCol = new WrSPListColumn(dispName, name, System.Data.DbType.String);
                    columns.Add(newCol);
                }

                return (WrColumn[])columns.ToArray(typeof(WrColumn));
            }
            catch (SoapException ex)
            {
                string detail = ex.Detail.OuterXml;
                throw new Exception(detail, ex);
            }
            catch (Exception ex)
            {
                if (log.IsWarnEnabled)
                    log.Warn("Error occured getting columns from SharePoint list data.", ex);
                throw;
            }
        }
        /// <summary>
        /// Gets all the fields in the SharePoint list identified by the specified table.
        /// </summary>
        /// <param name="table"></param>
        /// <param name="connStr"></param>
        /// <param name="owner"></param>
        /// <returns></returns>
		private WrColumn[] GetColumns(WrBaseTable table, string connStr, WrVendor.ELEM_OWNER owner)
        {
            // parse the connection string
            Hashtable keyPairs = SPListConnection.ParseConnectionString(connStr);
            this.spWebUrl = (string)keyPairs["URL"];
            WrCredentials credentials = WrSPListServer2.CreateWrCredentials(keyPairs);

            // run GetColumns(WrBaseTable, WrCredentials, WrVendor.ELEM_OWNER)
            return GetColumns(table, credentials, owner);
        }
 
        #endregion

        #region Helper Methods
        /// <summary>
        /// Creates a Lists object (SharePoint list web service) using the current database settings and the specified credentials.
        /// </summary>
        /// <param name="credentials"></param>
        /// <returns></returns>
        private Lists CreateListsService(WrCredentials credentials)
        {
            Lists listService = new Lists();
            listService.PreAuthenticate = true;
            if (string.IsNullOrEmpty(credentials.Domain))
                listService.Credentials = new NetworkCredential(credentials.Username, credentials.Password);
            else
                listService.Credentials = new NetworkCredential(credentials.Username, credentials.Password, credentials.Domain);
            listService.Url = spWebUrl + @"/_vti_bin/lists.asmx";
            return listService;
        }
        #endregion
    }
}
